﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using DTO;


namespace DAO
{
    
    public class NSX_DAO
    {
        //hiển thị DS
        public static DataTable hienthi()
        {
            SqlConnection cnn = sqlConnectionData.Ketnoi();
            cnn.Open();
            string sql = "select * from NHASX";
            SqlCommand cmd = new SqlCommand(sql, cnn);
            cmd.CommandType = CommandType.Text;
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataTable dtb = new DataTable();
            da.Fill(dtb);
            cnn.Close();
            return dtb;
        }
        public static DataTable timkiem(string Ma)
        {
            SqlConnection cnn = sqlConnectionData.Ketnoi();
            string sql = "select * from NHASX where MaNSX= @Ma ";
            SqlCommand cmd = new SqlCommand(sql, cnn);
            cmd.CommandType = CommandType.Text;
            cmd.Parameters.Add("@Ma", SqlDbType.NVarChar, 10);
            cmd.Parameters["@Ma"].Value = Ma;
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataTable dtb = new DataTable();
            da.Fill(dtb);
            return dtb;
        }
        public static DataTable timkiemNC(string Ma, string ten, string diachi, string sdt)
        {
            SqlConnection cnn = sqlConnectionData.Ketnoi();
            string sql = "select * from NHASX where MaNSX like '%" + Ma + "%' and TenNSX like'%" + ten + "%' and DiaChi like'%" + diachi + "%'  and SoDT like'%" + sdt + "%' ";
            SqlCommand cmd = new SqlCommand(sql, cnn);
            cmd.CommandType = CommandType.Text;
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataTable dtb = new DataTable();
            da.Fill(dtb);
            return dtb;
        }

        public static void ThemNSX(NSX_DTO nsx)
        {
            SqlConnection cnn = sqlConnectionData.Ketnoi();
            string sql = "insert into NHASX values(@ma,@ten,@diachi,@sdt)";
            SqlCommand cmd = new SqlCommand(sql, cnn);
            cmd.CommandType = CommandType.Text;
            cmd.Parameters.Add("@ma", SqlDbType.NVarChar, 10);
            cmd.Parameters.Add("@ten", SqlDbType.NVarChar, 100);
            cmd.Parameters.Add("@diachi", SqlDbType.NVarChar, 100);
            cmd.Parameters.Add("@sdt", SqlDbType.NVarChar, 15);

            cmd.Parameters["@ma"].Value = nsx.mansx;
            cmd.Parameters["@ten"].Value = nsx.tennsx;
            cmd.Parameters["@diachi"].Value = nsx.diachi;
            cmd.Parameters["@sdt"].Value = nsx.sodt;
            cnn.Open();
            cmd.ExecuteNonQuery();
            cnn.Close();
        }
        public static void SuaNSX(NSX_DTO nsx, string macu)
        {
            SqlConnection cnn = sqlConnectionData.Ketnoi();
            string sql = "update NHASX set MaNSX=@ma,TenNSX=@ten,DiaChi=@diachi,SoDT=@sdt where MaNSX='" + macu + "'";
            SqlCommand cmd = new SqlCommand(sql, cnn);
            cmd.CommandType = CommandType.Text;
            cmd.Parameters.Add("@ma", SqlDbType.NVarChar, 10);
            cmd.Parameters.Add("@ten", SqlDbType.NVarChar, 50);
            cmd.Parameters.Add("@diachi", SqlDbType.NVarChar, 10);
            cmd.Parameters.Add("@sdt", SqlDbType.NVarChar, 10);


            cmd.Parameters["@ma"].Value = nsx.mansx;
            cmd.Parameters["@ten"].Value = nsx.tennsx;
            cmd.Parameters["@diachi"].Value = nsx.diachi;
            cmd.Parameters["@sdt"].Value = nsx.sodt;
            cnn.Open();
            cmd.ExecuteNonQuery();
            cnn.Close();
        }
        public static void XoaNSX(string MaNSX)
        {
            SqlConnection cnn = sqlConnectionData.Ketnoi();
            string sql = "delete NHASX where MaNSX=@ma";
            SqlCommand cmd = new SqlCommand(sql, cnn);
            cmd.Parameters.Add("@ma", SqlDbType.NVarChar, 10);
            cmd.Parameters["@ma"].Value = MaNSX;
            cnn.Open();
            cmd.ExecuteNonQuery();
            cnn.Close();
        }
    }
}
